from typing import Optional
from fastapi import APIRouter, Depends, Query
from pydantic import BaseModel, Field
from sqlalchemy import text
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme

data_my_safe_05 = APIRouter(prefix="/phone", tags=["储物柜管理"], dependencies=[Depends(oauth2_scheme)])


class CabinetInventoryRankingSearch(PaginationBase):
    # 筛选条件
    fsiteNo: str = Field("", description="站点编号")
    ftype: str = Field("1", description="排行类型：1-降序，其他-升序，默认'1'")


@data_my_safe_05.get("/getRepertorySort", summary="储物柜-库存排行查询接口")
def get_repertory_sort(cabinet: CabinetInventoryRankingSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from i_warehouse_inv a,i_ts_good_info c"
    sql = sql + " where 1=1"
    sql = sql + " and a.fstate=2"
    sql = sql + " and a.fgoodno=c.fgoodno"
    if cabinet.fsiteNo != "":
        sql = sql + " and a.fsite_no= :fsiteNo "
        param["fsiteNo"] = cabinet.fsiteNo
    sql = sql + " group by a.fsite_no,a.fgoodno,c.fgoodname,c.fspec,c.funit"
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select a.fsite_no, a.fgoodno, c.fgoodname,cast(sum(fqty) as varchar) || c.funit fqty,c.fspec"
    sql = sql + " from i_warehouse_inv a,i_ts_good_info c"
    sql = sql + " where 1=1"
    sql = sql + " and a.fstate=2"
    sql = sql + " and a.fgoodno=c.fgoodno"
    if cabinet.fsiteNo != "":
        sql = sql + " and a.fsite_no= :fsiteNo "
        param["fsiteNo"] = cabinet.fsiteNo
    sql = sql + " group by a.fsite_no,a.fgoodno,c.fgoodname,c.fspec,c.funit"
    sql = sql + " order by sum(fqty)  "
    if cabinet.ftype == "1":
        sql = sql + " desc "
    sql = sql + " limit :limit offset :offset "
    param["limit"] = cabinet.limit
    param["offset"] = cabinet.offset
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fsiteNo'] = data[key][0]
        d1['fgoodno'] = data[key][1]
        d1['fgoodname'] = data[key][2]
        d1['fqty'] = data[key][3]
        d1['fspec'] = data[key][4]
        data_list.append(d1)

    info_dict = {"list": data_list, "limit": cabinet.limit, "page": cabinet.pageIndex,
                 "pages": (count + cabinet.limit - 1) // cabinet.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)
